// 引进mysql
const mysql = require('mysql')
// 获取数据库信息
const config = require('./db.config')
// 建立连接池
const pool = mysql.createPool(config)

class DB_blog {
  constructor(pool = null) {
    this.pool = pool
  }

  // 查询方法，参数为查询的 sql 语句
  query(sql) {
    const _this = this
    return new Promise(async (resolve, reject) => {
      _this.pool.getConnection((err, connection) => {
        if (err) reject('DATABASE ERROR, connection faild!')
        connection.query(sql, (error, result, fields) => {
          connection.release()
          if (error) reject('ERROR, ' + error.message)
          resolve(result, fields)
        })
      })
    })
  }

  // 获取一级目录
  getMenu() {
    const _this = this
    return new Promise(async (resolve, reject) => {
      const selectSql = `
        SELECT
        blog_menu_id, blog_menu_name, blog_menu_path
        FROM
        blog_menu
      `
      const selectResult = await _this.query(selectSql)
      if (selectResult.length > 0) resolve(selectResult)
      reject(selectResult)
    })
  }

  // 获取前blog_article表的十条数据
  getBeforeArticle() {
    const _this = this
    return new Promise(async (resolve, reject) => {
      const selectSql = `
        SELECT
        b_a.blog_article_id, b_a.blog_article_title, b_a.blog_article_ctime, b_s.blog_submenu_name
        FROM
        blog_article AS b_a
        LEFT JOIN
        blog_submenu AS b_s
        ON
        b_s.blog_submenu_id = b_a.blog_key_submenu_id
        ORDER BY
        b_a.blog_article_id
        DESC
        LIMIT 10
      `
      const selectResult = await _this.query(selectSql)
      if (selectResult.length > 0) resolve(selectResult)
      reject(selectResult)
    })
  }

  // 获取指定 id 的文章路径和文章对应类型
  getArticleById(id) {
    const _this = this
    return new Promise(async (resolve, reject) => {
      const selectSql = `
        SELECT
        b_a.blog_article_content, b_s.blog_submenu_name
        FROM
        blog_submenu AS b_s
        LEFT JOIN
        blog_article AS b_a
        ON
        b_s.blog_submenu_id = b_a.blog_key_submenu_id
        WHERE
        b_a.blog_article_id = '${id}'
      `
      const selectResult = await _this.query(selectSql)
      if (selectResult.length > 0) resolve(selectResult)
      reject(selectResult)
    })
  }

  // 获取 blog_admin 中的内容
  getAdmin() {
    const _this = this
    return new Promise(async (resolve, reject) => {
      const selectSql = `
        SELECT
        blog_nickname, blog_develop_exp, blog_major, blog_develop_dir, blog_motto, blog_hobby
        FROM
        blog_admin
      `
      const selectResult = await _this.query(selectSql)
      if (selectResult.length > 0) resolve(selectResult)
      reject(selectResult)
    })
  }

  // 获取 blog_submenu 中的内容
  getStudySubmenu() {
    const _this = this
    return new Promise(async (resolve, reject) => {
      const selectSql = `
        SELECT
        blog_submenu_id, blog_submenu_name
        FROM
        blog_submenu
      `
      const selectResult = await _this.query(selectSql)
      if (selectResult.length > 0) {
        const article = await _this.getArticle()
        for (let i of selectResult.keys()) {
          const data = []
          for (let index of article.keys())
            if (selectResult[i].blog_submenu_id === article[index].blog_key_submenu_id)
              data.push(article[index])
          selectResult[i].article = data
        }
        resolve(selectResult)
      }
      reject(selectResult)
    })
  }

  // 获取 blog_article表 中的内容
  getArticle() {
    const _this = this
    return new Promise(async (resolve, reject) => {
      const selectSql = `
        SELECT
        blog_article_id, blog_article_title, blog_key_submenu_id, blog_article_ctime
        FROM
        blog_article
      `
      const selectResult = await _this.query(selectSql)
      if (selectResult.length > 0) resolve(selectResult)
      reject(selectResult)
    })
  }
}

const db = new DB_blog(pool)

module.exports = db